キーペア認証を使ってSnowSQLでSnowflakeへ接続してみた

「カスタマーストーリー」を支えるツール特集

#Snowflake

大高大輔

2021.03.10

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは！DA(データアナリティクス)事業本部インテグレーション部の大高です。

Snowflakeではユーザー名、パスワードによる基本認証の他に、キーペア認証がサポートされています。キーペアとしては2048bit以上のRSAキーペアがサポートされており、公開鍵・秘密鍵を生成して設定することで、Snowflakeクライアントからキーペアを利用してSnowflakeへ接続することができます。

これまではキーペア認証をしたことがなかったので、今回こちらをドキュメントに沿って実際に試してみたいと思います。

前提

今回はSnowSQLを利用してキーペア認証を試してみたいと思います。SnowSQLについては事前にインストール済みの環境となります。

SnowSQLの利用についてはこちらのドキュメントに記載されています。

また、OSはMacOSを利用しています。Windowsは試せていませんが、WSLで同様に設定できるかと思います。

サポート対象の確認

2021/03/09現在、各クライアントにおけるキーペア認証のサポート状況は以下のとおりです。

クライアント	キーペア認証	キーペアローテーション	暗号化されていない秘密鍵
SnowSQL （CLI クライアント）	○	○	✗
Python用Snowflakeコネクター	○	○	○
Spark用Snowflakeコネクター	○	○	○
Kafka用Snowflakeコネクター	○	○	○
Go Driver	○	○	✗
JDBC ドライバー	○	○	○
ODBC ドライバー	○	○	○
Node.jsドライバー	✗	✗	✗
.NET ドライバー	✗	✗	✗

今回はSnowSQLで検証をしてみたいと思いますので、秘密鍵は暗号化が必要となりますね。

キーペア認証の構成

では、対象を確認したところで実際にキーペア認証の設定をすすめていきたいと思います。

ステップ1：秘密鍵を生成する

今回は確認した通り「暗号化された秘密鍵」が必要となるのでパスフレーズが必要となります。また、設定したパスフレーズは初回のSnowflake接続時に必要となります。

パスフレーズの要件として、PCI DSS 標準に基づいたパスフレーズを生成することが推奨されているので、PCI DSS v3.2.1における下記条件を満たすようにパスフレーズを設定します。

パスワードに7文字以上が含まれる
数字と英文字の両方を含む

また、ファイルの保存場所が悩ましいですが今回は以下のように~/.snowsql/keysというディレクトリを作成して、その中に保存するようにします。

$ mkdir ~/.snowsql/keys

ディレクトリが準備できたら、下記コマンドで秘密鍵を生成します。

# ディレクトリへ移動
$ cd ~/.snowsql/keys

# 秘密鍵を生成
$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8
Generating RSA private key, 2048 bit long modulus (2 primes)
..........+++++
.....................................................+++++
e is 65537 (0x010001)
Enter Encryption Password: # ここで複雑なパスフレーズを入力
Verifying - Enter Encryption Password: # ここで複雑なパスフレーズを入力

# 生成されたファイルを確認
$ cat rsa_key.p8
-----BEGIN ENCRYPTED PRIVATE KEY-----
MIIFLTBXBgkqhkiG9w0BBQ0wSjApBgkqhkiG9w0BBQwwHAQIBG/EDdVHFjICAggA
MAwGCCqGSIb3DQIJBQAwHQYJYIZIAWUDBAEqBBBt+GcCWcGME4phGUQicpV2BIIZ
...
-----END ENCRYPTED PRIVATE KEY-----

ステップ2：公開鍵を生成する

つぎに、秘密鍵から公開鍵を生成します。

# ディレクトリへ移動
$ cd ~/.snowsql/keys

# 公開鍵を生成
$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
Enter pass phrase for rsa_key.p8: # パスフレーズを入力
writing RSA key

# 生成されたファイルを確認
$ cat rsa_key.pub
-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAwXhzQeg8ohVY9tqTceRh
D4WsFQewuddtt3tSfOFxeSNEPN9TCNvKRBvhcciSxLBQb8uqkrPJP6Pr7w9pej+Z
...
-----END PUBLIC KEY-----

ステップ3：秘密鍵と公開鍵を安全に保管する

生成時に利用したいディレクトリ内で生成したので、今回は特にファイルの移動はしません。

秘密鍵のパーミッションだけ少し修正しておきます。

# ディレクトリへ移動
$ cd ~/.snowsql/keys

# パーミッション確認
$ ls -ogh
total 16
-rw-------  1    1.8K  3  9 09:40 rsa_key.p8
-rw-r--r--  1    451B  3  9 09:45 rsa_key.pub

# 秘密鍵のパーミッションを変更
$ chmod 400 rsa_key.pub

# パーミッション確認
$ ls -ogh
total 16
-r--------  1    1.8K  3  9 09:40 rsa_key.p8
-rw-r--r--  1    451B  3  9 09:45 rsa_key.pub

ステップ4：Snowflakeユーザーに公開鍵を割り当てる

割り当てる際のSQLコマンドでは連続した文字列で公開鍵を指定することになるので、まずは事前に以下のコマンドで出力される必要な文字列を控えておきます。

$ cat rsa_key.pub | \
tr -d "\n" | \
sed "s/-----BEGIN PUBLIC KEY-----//g" | \
sed "s/-----END PUBLIC KEY-----//g"
MIIBIjANBgkqhkiG9w0BAQEFA... # これを控えておく

つぎにSnowflakeに接続して、ユーザーに公開鍵を割り当てます。

割り当てにはSECURITYADMINロール以上が必要となります。なお、ユーザーを誤ってACCOUNTADMINで作成してしまっているような場合には権限不足となるので、該当ユーザのOWNERを適切なロールであるUSERADMINに変更しておく必要があります。

$ snowsql
❄ SnowSQL ❄ v1.2.12
Type SQL statements or !help
foo_bar#(~略~)>USE ROLE SECURITYADMIN;
╒══════════════════════════════════╕
│ status                           │
╞══════════════════════════════════╡
│ Statement executed successfully. │
╘══════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.105s
foo_bar#(~略~)>ALTER USER foo_bar SET rsa_public_key='MIIBIjANBgkqhkiG9w0BAQEFA...';
╒══════════════════════════════════╕
│ status                           │
╞══════════════════════════════════╡
│ Statement executed successfully. │
╘══════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.091s

ステップ5：ユーザーの公開鍵のフィンガープリントを検証する

続けて、公開鍵のフィンガープリントを確認しておきます。

foo_bar#(no warehouse)@(no database).(no schema)>DESC USER foo_bar;
╒═══════════════════════════════╤═════════════════════════════════════════════════════╤═════════╤══════════════════════════════════════════════╕
│ property                      │ value                                               │ default │ description                                  │
╞═══════════════════════════════╪═════════════════════════════════════════════════════╪═════════╪══════════════════════════════════════════════╡
│ NAME                          │ FOO_BAR                                             │ null    │ Name                                         │
├───────────────────────────────┼─────────────────────────────────────────────────────┼─────────┼──────────────────────────────────────────────┤
...
├───────────────────────────────┼─────────────────────────────────────────────────────┼─────────┼──────────────────────────────────────────────┤
│ RSA_PUBLIC_KEY_FP             │ SHA256:TiC4NxC1a3Jzr7A7VTg2/I5io4UC84XlZ/01py4RytY= │ null    │ Fingerprint of user's RSA public key.        │
├───────────────────────────────┼─────────────────────────────────────────────────────┼─────────┼──────────────────────────────────────────────┤
│ RSA_PUBLIC_KEY_2_FP           │ null                                                │ null    │ Fingerprint of user's second RSA public key. │
├───────────────────────────────┼─────────────────────────────────────────────────────┼─────────┼──────────────────────────────────────────────┤
...
╘═══════════════════════════════╧═════════════════════════════════════════════════════╧═════════╧══════════════════════════════════════════════╛
25 Row(s) produced. Time Elapsed: 0.114s
foo_bar#(no warehouse)@(no database).(no schema)>!quit
Goodbye!

ステップ6：キーペア認証を使用するようにSnowflakeクライアントを構成する

最後に、実際にSnowSQLでキーペア認証を使用するように設定を変更します。

SnowSQLを介した接続 — Snowflake Documentation

ドキュメントによると

構成ファイルまたはコマンドラインのいずれかで、秘密キーファイルへのパスを指定します。

とあるので、今回は「構成ファイル」で設定を行います。

まずは環境変数へパスフレーズを設定します。私の環境はFish Shellを利用しているので、 ~/.config/fish/config.fish へ下記を追加して反映させましたが、bashであれば ~/.bash_profile に追記して source ~/.bash_profile で反映する流れになると思います。

# SnowSQL KeyPair Connection Settings
export SNOWSQL_PRIVATE_KEY_PASSPHRASE='<passphrase>'

次に「構成ファイル」である~/.snowsql/configファイルを設定します。

このファイルでは、わたしは事前に以下のように接続設定を構成していました。

[connections.foobar]
accountname = foobar.ap-northeast-1.aws
username = foo
password = bar

これを以下のように修正します。

[connections.foobar]
accountname = foobar.ap-northeast-1.aws
username = foo
private_key_path = /Users/ootaka.daisuke/.snowsql/keys/rsa_key.p8
# password = bar

この状態で、接続してみます。

snowsql -c foobar
❄ SnowSQL ❄ v1.2.12
Type SQL statements or !help
foo#(no warehouse)@(no database).(no schema)>!quit
Goodbye!

キーペア認証できましたね！

まとめ

以上、キーペア認証を使ってSnowSQLでSnowflakeへ接続してみました。

SnowSQLによる接続を簡略化したい場合、「構成ファイル」（~/.snowsql/config）にパスワードを平文で書くことになるので、キーペア認証にしておけば、より一層セキュアにできそうです。また、Snowpipe REST APIを使用したデータロードの際にもキーペア認証の設定は必要となるので、この設定方法は覚えておくとよさそうですね。（実は本当はこっちを書きたかったりしたのですが、次の機会に…）

どなたかのお役に立てば幸いです。それでは！